基础数据操作之四 从SQL的case、group by、join 到工具 KNIME

之前的作业 之二

让我们从之前的数据和剩下的作业继续聊起。

示例数据

6. 想把上一个问题中得到的表做的更人性化一点，即把"班级"一列中的，"1"改为"一班"，"2"改为"二班"；
7. "李二"缺失的身高怎么办呢？
8. 根据英语成绩，由高到低，把两个班的同学排序；
9. 得到各个班英语和数学的平均成绩；
10. 把上一个问题中得到的平均成绩增加到原表的最后一行中；
11. 得到各个班级中，英语最高的同学名单；
12. 得到所有女生当中，英语成绩最高的同学的姓名与班级；
13. 根据数学成绩把同学区分为优（大于90），良（小于等于90），并计算有多少优，有多少良；
14. 怎么把有问题的出生年月改过来？
15. 出生年月改过来之后，怎么计算年龄？

上节我们通过SQL以及KNIME完成了部分 select 相关操作的学习，这一节我们仍然按照 最小完备 的理念看一下SQL的其他部分。

CASE，NULL，和 order by

SQL有很多的操作，而且不同的 SQL 有着不同的方言（dialect，比如MySQL，Oracle和SQL Server的语法细节就有差别），虽然 SQL 功能很强大，但我个人建议，在面对一些具体的问题时，还是要考虑自己手头的工具是不是最适合完成当前问题的工具，换句话来说，对于一个完整的工作，需要考虑哪些放在数据库中通过 SQL 完成较好，哪些放在其他工具或者程序中完成较好。

比如对于上面6中的替换问题，如果在 SQL 中写，那么就需要类似一些计算机语言中的switch-case, 或是 if 语句或是更复杂的正则表达式做匹配与条件判断。

select
    CASE
     WHEN 班级=1 THEN '一班'，
     WHEN 班级=2 THEN '二班'，
     WHEN 班级=3 THEN '三班'，
    END,
    姓名
  from sampleData

如果中间判断的情况复杂一些，或者需求有变动，比如现在想把"一班"和"二班"改名叫理科班，"三班"改名叫文科班，你通过CASE语句进行了改动，结果你改完之后，有人又要求还是用原来"一班"，"二班"，"三班"的分类方法，对于这种需求变动比较频繁的任务，纯粹使用SQL，就会比较恼人了，另外较长的SQL也会带来一定的调试问题以及维护问题。

对于上面问题7中的缺失问题: 从逻辑上讲，我们可能会这么填充缺失的身高值，A. 填充一个固定的值，比如一个平均身高值，或出现最多次数的身高值；B. 填充和他相邻的前一个人或后一个人的身高值；C. 或者把这一行去掉。总的来说，就这几种方法，对于缺失的身高来说，填一个平均值可能比较合理，但对于其他类型的缺失值，需要根据具体情况处理。

在MySQL中，使用 IFNULL(身高, 165) 来将缺失值（英文缺失称为Null）填补为165，而在SQL Server中使用 ISNULL(身高, 165) 来填充缺失值。同样都是SQL，但他们一个使用来 IFNULL，一个使用了 ISNULL，这就是之前所说SQL中的方言（dialect）问题。

问题8中的排序，SQL中只需要用 order by 英语 desc 就可以降序排列了，升序的话也有相应的语法。在这个问题中，一次排序就解决了问题，可是在一些其他情况下，比如假设有很多人英语成绩相同，但是他们的数学成绩不同，我们仍然想先按照英语成绩排序，但想把英语成绩相同的人，再按照数学成绩由高到底排序，这种就需要多次排序才能解决问题。多次排序，也会带来相应的调试问题以及维护问题。

Group by

Group是分组的意思，Group by 顾名思义就是根据 By 后面的内容对数据进行分组，并对同一个组的数据进行操作，计算出一个结果，这个计算的函数叫做Group Functions或Aggregate Functions 聚合函数。

问题9中，求各个班的平均成绩，就是一个典型的 Group by 操作，其中函数AVG就是用来计算平均值的聚合函数：

select 班级，AVG(英语), AVG(数学) from sampleData group by 班级

Group By 图示

可以想象，这种聚合函数还有很多，比如一组中取最大值，取第一个值，求和，求个数等等，只要能操作一组数据，得到一个结果的，都可以成为聚合函数，只不过就是各种SQL方言对聚合函数的种类支持有所差异罢了。

子查询 subquery

有的时候，查询条件比较复杂，想要得到最终结果，需要先得到A集合，再从A集合中查询，得到最终结果B，这种情况A集合相关的查询就是subquery了。比如下面SQL语句：

SELECT column-names
  FROM table-name1
 WHERE value IN (SELECT column-name
                   FROM table-name2
                  WHERE condition)

就是先从 table-name2 中获得结果，然后再利用这个结果做为条件，从 table-name1 表中获得进一步的结果。

JOIN

除了上面的 subquery 概念可以用在多表查询上，SQL中的 JOIN 语法是多表查询的利器。JOIN其实对应的是初中数学中集合的概念，比如我们通过两个不同查询分别获得了集合A，和集合B，现在需要属于集合A但不属于集合B的结果，再或者属于A且又属于B，属于A或属于B，等等，就需要用到 JOIN，以及 JOIN 衍生出来的一些操作了。这一部分，只要我们搞清楚逻辑关系，现用现查即可。如果想了解 SQL 中 JOIN 更细节的内容，推荐阅读陈皓博客上的一篇叫做"图解 SQL 的 JOIN"的文章。

用 KNIME 完成上面的一个作业

至此，我们已经基本覆盖了 SQL 中相应的操作，如果这些操作在 KNIME 中都可以完成，那么我们就可以说，在某些方面，KNIME 拥有和 SQL 同样"完备"的操作数据的能力。

以13题为例：

13. 根据数学成绩把同学区分为优（大于90），良（小于等于90），并计算有多少优，有多少良；

通过前面我们介绍SQL的操作来看，我们要先有一个类似 CASE 的语句判断数学成绩，形成第一部分结果，然后再利用 subquery 概念，对之前结果进行一个类似 SQL 中 group by 的操作，就可以得到最终结果了，好，让我们来看一下在 KNIME 中怎么做。

首先，在原始数据后面插入 rule engine 规则引擎节点：

插入 rule engine 规则引擎节点

其次，配置 rule engine 规则引擎节点，一条规则写一行，其中的 $数学$ 代表数学那一列，新生成"数学评价"一列：

配置 rule engine 规则引擎节点

运行节点并查看结果：

第一部分结果

搜索加入group by 分组聚合节点：

加入group by 分组聚合节点

配置group by，by 什么？对，by 数学评价：

配置 group by - 1

配置 group by 的聚合函数，从截图中可以看出，有很多聚合函数可供选择，这里我们选 count 来计数：

配置group by - 2

运行得到最终结果：

13题最终结果

从整个过程我们可以看到，KNIME 非常容易调试中间结果，也非常灵活。